﻿Imports Tohto.Base.Entity
Imports Oracle.DataAccess.Client
Imports Tohto.Base.DBUtility
Imports System.Text
Imports Tohto.Base.CommonConst

Public Class KJ0030B01

    Public Function Fetch_LIST(ByVal strI_VC_LOGINID As String _
                              , ByVal strI_VC_POST_CD As String _
                              , ByVal strI_VC_EMP_CD As String _
                              , ByVal strI_VC_KOJI_KB As String _
                              , ByVal strI_DT_KANRYO_YOTEI_DT_FROM As String _
                              , ByVal strI_DT_KANRYO_YOTEI_DT_TO As String _
                              , ByVal strI_DT_KOJI_KANRYO_DT_FROM As String _
                              , ByVal strI_DT_KOJI_KANRYO_DT_TO As String _
                              , ByVal strI_VC_KOJI_ST As String _
                              , ByVal strI_VC_KOJI_YOTEI As String _
                              , ByRef errInfo As PLSQLErrorInfo) As DataSet

        errInfo.Clear()

        Dim strCmdText As String
        strCmdText = "PG_KJ0030.FETCH_LIST"

        Dim parameters(12) As OracleParameter

        Dim I_VC_LOGINID As New OracleParameter("I_VC_LOGINID", OracleDbType.Varchar2, ParameterDirection.Input)
        I_VC_LOGINID.Value = strI_VC_LOGINID

        Dim I_VC_POST_CD As New OracleParameter("I_VC_POST_CD", OracleDbType.Varchar2, ParameterDirection.Input)
        I_VC_POST_CD.Value = strI_VC_POST_CD

        Dim I_VC_EMP_CD As New OracleParameter("I_VC_EMP_CD", OracleDbType.Varchar2, ParameterDirection.Input)
        I_VC_EMP_CD.Value = strI_VC_EMP_CD

        Dim I_VC_KOJI_KB As New OracleParameter("I_VC_KOJI_KB", OracleDbType.Varchar2, ParameterDirection.Input)
        I_VC_KOJI_KB.Value = strI_VC_KOJI_KB

        Dim I_DT_KANRYO_YOTEI_DT_FROM As New OracleParameter("I_DT_KANRYO_YOTEI_DT_FROM", OracleDbType.Varchar2, ParameterDirection.Input)
        I_DT_KANRYO_YOTEI_DT_FROM.Value = strI_DT_KANRYO_YOTEI_DT_FROM

        Dim I_DT_KANRYO_YOTEI_DT_TO As New OracleParameter("I_DT_KANRYO_YOTEI_DT_TO", OracleDbType.Varchar2, ParameterDirection.Input)
        I_DT_KANRYO_YOTEI_DT_TO.Value = strI_DT_KANRYO_YOTEI_DT_TO

        Dim I_DT_KOJI_KANRYO_DT_FROM As New OracleParameter("I_DT_KOJI_KANRYO_DT_FROM", OracleDbType.Varchar2, ParameterDirection.Input)
        I_DT_KOJI_KANRYO_DT_FROM.Value = strI_DT_KOJI_KANRYO_DT_FROM

        Dim I_DT_KOJI_KANRYO_DT_TO As New OracleParameter("I_DT_KOJI_KANRYO_DT_TO", OracleDbType.Varchar2, ParameterDirection.Input)
        I_DT_KOJI_KANRYO_DT_TO.Value = strI_DT_KOJI_KANRYO_DT_TO

        Dim I_VC_KOJI_ST As New OracleParameter("I_VC_KOJI_ST", OracleDbType.Varchar2, ParameterDirection.Input)
        I_VC_KOJI_ST.Value = strI_VC_KOJI_ST

        Dim I_VC_KOJI_YOTEI As New OracleParameter("I_VC_KOJI_YOTEI", OracleDbType.Varchar2, ParameterDirection.Input)
        If strI_VC_KOJI_YOTEI.Equals("True") Then
            I_VC_KOJI_YOTEI.Value = "1"
        Else
            I_VC_KOJI_YOTEI.Value = "0"
        End If

        Dim O_CU_DATA As New OracleParameter("O_CU_DATA", OracleDbType.RefCursor, ParameterDirection.Output)
        Dim O_CU_ERR As New OracleParameter("O_CU_ERR", OracleDbType.RefCursor, ParameterDirection.Output)

        Dim O_STATS As New OracleParameter("O_STATS", OracleDbType.Int32, ParameterDirection.ReturnValue)

        parameters(0) = I_VC_LOGINID
        parameters(1) = I_VC_POST_CD
        parameters(2) = I_VC_EMP_CD
        parameters(3) = I_VC_KOJI_KB
        parameters(4) = I_DT_KANRYO_YOTEI_DT_FROM
        parameters(5) = I_DT_KANRYO_YOTEI_DT_TO
        parameters(6) = I_DT_KOJI_KANRYO_DT_FROM
        parameters(7) = I_DT_KOJI_KANRYO_DT_TO
        parameters(8) = I_VC_KOJI_ST
        parameters(9) = I_VC_KOJI_YOTEI
        parameters(10) = O_CU_DATA
        parameters(11) = O_CU_ERR
        parameters(12) = O_STATS

        Dim ds As DataSet = Nothing
        Dim tableNames(0) As String
        tableNames(0) = "KOJI_IKKATU"

        ds = OracleHelp.FillTable(OracleHelp.ConnectionStringLocalTransaction,
                                        CommandType.StoredProcedure,
                                        strCmdText,
                                        errInfo,
                                        parameters,
                                        tableNames)

        Return ds

    End Function

    Public Sub SAVE_DATA(ByVal strI_VC_LOGINID As String, _
                              ByVal strI_DT_KANRYO As String, _
                              ByVal strI_DT_SIHARAI_YT As String, _
                              ByVal dtupdate As DataTable, _
                              ByRef errInfo As PLSQLErrorInfo)
        Dim OracleCon As New OracleConnection(OracleHelp.ConnectionStringLocalTransaction)
        OracleCon.Open()
        Dim OracleTrans As OracleTransaction = OracleCon.BeginTransaction()
        Try

            SAVE_DATA_YIKO(strI_VC_LOGINID, strI_DT_KANRYO, strI_DT_SIHARAI_YT, dtupdate, OracleTrans, errInfo)

            If errInfo.RETCD = ComConst.ORA_RET_OK Then
                OracleTrans.Commit()
            ElseIf errInfo.RETCD = ComConst.ORA_RET_NG Then
                OracleTrans.Rollback()
            End If

        Catch ex As Exception
            OracleTrans.Rollback()
            Throw ex
        End Try

    End Sub

    Public Sub SAVE_DATA_YIKO(ByVal strI_VC_LOGINID As String, _
                              ByVal strI_DT_KANRYO As String, _
                              ByVal strI_DT_SIHARAI_YT As String, _
                              ByVal dtupdate As DataTable, _
                              ByVal OracleTrans As OracleTransaction, _
                              ByRef errInfo As PLSQLErrorInfo)

        'SQL
        Dim TempTableSQL As StringBuilder = tempSQLwrite()
        For i As Integer = 0 To dtupdate.Rows.Count - 1
            Dim TableParameters As OracleParameter() = parameterTabless(dtupdate, i)

            OracleHelp.ExecuteNonQuery(OracleTrans, CommandType.Text, TempTableSQL.ToString, TableParameters)
        Next

        errInfo.Clear()

        Dim strCmdText As String
        strCmdText = "PG_KJ0030.SAVE_DATA"

        Dim parameters(4) As OracleParameter

        Dim I_VC_LOGINID As New OracleParameter("I_VC_LOGINID", OracleDbType.Varchar2, ParameterDirection.Input)
        I_VC_LOGINID.Value = strI_VC_LOGINID

        Dim I_DT_KANRYO As New OracleParameter("I_DT_KANRYO", OracleDbType.Varchar2, ParameterDirection.Input)
        I_DT_KANRYO.Value = strI_DT_KANRYO

        Dim I_DT_SIHARAI_YT As New OracleParameter("I_DT_SIHARAI_YT", OracleDbType.Varchar2, ParameterDirection.Input)
        I_DT_SIHARAI_YT.Value = strI_DT_SIHARAI_YT

        Dim O_CU_ERR As New OracleParameter("O_CU_ERR", OracleDbType.RefCursor, ParameterDirection.Output)

        Dim O_STATS As New OracleParameter("O_STATS", OracleDbType.Int32, ParameterDirection.ReturnValue)

        parameters(0) = I_VC_LOGINID
        parameters(1) = I_DT_KANRYO
        parameters(2) = I_DT_SIHARAI_YT
        parameters(3) = O_CU_ERR
        parameters(4) = O_STATS


    End Sub

    Private Function tempSQLwrite() As StringBuilder

        Dim insertSql As StringBuilder = New StringBuilder
        insertSql.AppendLine("INSERT INTO  ")
        insertSql.AppendLine("            T_KJ0030T0(")
        insertSql.AppendLine("            ROWID_ANKEN")
        insertSql.AppendLine("           , ROWID_MITUMORI")
        insertSql.AppendLine("           , ROWID_HACCHU")
        insertSql.AppendLine("           , TAISHO_FG")
        insertSql.AppendLine("           , GYOSHA_CD")
        insertSql.AppendLine("           , GYOSHA_NM")
        insertSql.AppendLine("           , TEHAI_DT")
        insertSql.AppendLine("           , HACCHU_DT")
        insertSql.AppendLine("           , KOJI_NO")
        insertSql.AppendLine("           , KOJI_ST")
        insertSql.AppendLine("           , KOJI_NM")
        insertSql.AppendLine("           , POST_NM_KANJI")
        insertSql.AppendLine("           , NM_KANJI_CONNECTION")
        insertSql.AppendLine("           , ARTICLE_CD")
        insertSql.AppendLine("           , ARTICLE_NM")
        insertSql.AppendLine("           , ROOM_CD")
        insertSql.AppendLine("           , ROOM_NM")
        insertSql.AppendLine("           , HACCHU_KINGAKU")
        insertSql.AppendLine("           , SEIKYU_KINGAKU")
        insertSql.AppendLine("           , MITUMORI_NO")
        insertSql.AppendLine("             )")
        insertSql.AppendLine("   VALUES(    ")
        insertSql.AppendLine("            :ROWID_ANKEN")
        insertSql.AppendLine("           , :ROWID_MITUMORI")
        insertSql.AppendLine("           , :ROWID_HACCHU")
        insertSql.AppendLine("           , :TAISHO_FG")
        insertSql.AppendLine("           , :GYOSHA_CD")
        insertSql.AppendLine("           , :GYOSHA_NM")
        insertSql.AppendLine("           , :TEHAI_DT")
        insertSql.AppendLine("           , :HACCHU_DT")
        insertSql.AppendLine("           , :KOJI_NO")
        insertSql.AppendLine("           , :KOJI_ST")
        insertSql.AppendLine("           , :KOJI_NM")
        insertSql.AppendLine("           , :POST_NM_KANJI")
        insertSql.AppendLine("           , :NM_KANJI_CONNECTION")
        insertSql.AppendLine("           , :ARTICLE_CD")
        insertSql.AppendLine("           , :ARTICLE_NM")
        insertSql.AppendLine("           , :ROOM_CD")
        insertSql.AppendLine("           , :ROOM_NM")
        insertSql.AppendLine("           , :HACCHU_KINGAKU")
        insertSql.AppendLine("           , :SEIKYU_KINGAKU")
        insertSql.AppendLine("           , :MITUMORI_NO)")

        Return insertSql
    End Function

    Private Function parameterTabless(ByVal datas As DataTable, ByVal i As Integer)

        Dim parameters(19) As OracleParameter

        Dim ROWID_ANKEN As New OracleParameter("ROWID_ANKEN", OracleDbType.Varchar2)
        Dim ROWID_MITUMORI As New OracleParameter("ROWID_MITUMORI", OracleDbType.Varchar2)
        Dim ROWID_HACCHU As New OracleParameter("ROWID_HACCHU", OracleDbType.Varchar2)
        Dim TAISHO_FG As New OracleParameter("TAISHO_FG", OracleDbType.Varchar2)
        Dim GYOSHA_CD As New OracleParameter("GYOSHA_CD", OracleDbType.Varchar2)
        Dim GYOSHA_NM As New OracleParameter("GYOSHA_NM", OracleDbType.Varchar2)
        Dim TEHAI_DT As New OracleParameter("TEHAI_DT", OracleDbType.Date)
        Dim HACCHU_DT As New OracleParameter("HACCHU_DT", OracleDbType.Date)
        Dim KOJI_NO As New OracleParameter("KOJI_NO", OracleDbType.Varchar2)
        Dim KOJI_ST As New OracleParameter("KOJI_ST", OracleDbType.Varchar2)
        Dim KOJI_NM As New OracleParameter("KOJI_NM", OracleDbType.Varchar2)
        Dim POST_NM_KANJI As New OracleParameter("POST_NM_KANJI", OracleDbType.Varchar2)
        Dim NM_KANJI_CONNECTION As New OracleParameter("NM_KANJI_CONNECTION", OracleDbType.Varchar2)
        Dim ARTICLE_CD As New OracleParameter("ARTICLE_CD", OracleDbType.Varchar2)
        Dim ARTICLE_NM As New OracleParameter("ARTICLE_NM", OracleDbType.Varchar2)
        Dim ROOM_CD As New OracleParameter("ROOM_CD", OracleDbType.Varchar2)
        Dim ROOM_NM As New OracleParameter("ROOM_NM", OracleDbType.Varchar2)
        Dim HACCHU_KINGAKU As New OracleParameter("HACCHU_KINGAKU", OracleDbType.Int32)
        Dim SEIKYU_KINGAKU As New OracleParameter("SEIKYU_KINGAKU", OracleDbType.Int32)
        Dim MITUMORI_NO As New OracleParameter("MITUMORI_NO", OracleDbType.Int32)

        ROWID_ANKEN.Value = datas.Rows(i)("ROWID_ANKEN")
        ROWID_MITUMORI.Value = datas.Rows(i)("ROWID_MITUMORI")
        ROWID_HACCHU.Value = datas.Rows(i)("ROWID_HACCHU")
        TAISHO_FG.Value = datas.Rows(i)("TAISHO_FG")
        GYOSHA_CD.Value = datas.Rows(i)("GYOSHA_CD")
        GYOSHA_NM.Value = datas.Rows(i)("GYOSHA_NM")
        TEHAI_DT.Value = datas.Rows(i)("TEHAI_DT")
        HACCHU_DT.Value = datas.Rows(i)("HACCHU_DT")
        KOJI_NO.Value = datas.Rows(i)("KOJI_NO")
        KOJI_ST.Value = datas.Rows(i)("KOJI_ST")
        KOJI_NM.Value = datas.Rows(i)("KOJI_NM")
        POST_NM_KANJI.Value = datas.Rows(i)("POST_NM_KANJI")
        NM_KANJI_CONNECTION.Value = datas.Rows(i)("NM_KANJI_CONNECTION")
        ARTICLE_CD.Value = datas.Rows(i)("ARTICLE_CD")
        ARTICLE_NM.Value = datas.Rows(i)("ARTICLE_NM")
        ROOM_CD.Value = datas.Rows(i)("ROOM_CD")
        ROOM_NM.Value = datas.Rows(i)("ROOM_NM")
        HACCHU_KINGAKU.Value = datas.Rows(i)("HACCHU_KINGAKU")
        SEIKYU_KINGAKU.Value = datas.Rows(i)("SEIKYU_KINGAKU")
        MITUMORI_NO.Value = datas.Rows(i)("MITUMORI_NO")

        parameters(0) = ROWID_ANKEN
        parameters(1) = ROWID_MITUMORI
        parameters(2) = ROWID_HACCHU
        parameters(3) = TAISHO_FG
        parameters(4) = GYOSHA_CD
        parameters(5) = GYOSHA_NM
        parameters(6) = TEHAI_DT
        parameters(7) = HACCHU_DT
        parameters(8) = KOJI_NO
        parameters(9) = KOJI_ST
        parameters(10) = KOJI_NM
        parameters(11) = POST_NM_KANJI
        parameters(12) = NM_KANJI_CONNECTION
        parameters(13) = ARTICLE_CD
        parameters(14) = ARTICLE_NM
        parameters(15) = ROOM_CD
        parameters(16) = ROOM_NM
        parameters(17) = HACCHU_KINGAKU
        parameters(18) = SEIKYU_KINGAKU
        parameters(19) = MITUMORI_NO

        Return parameters
    End Function


End Class
